﻿using Clirelmgt.Manager;
using Clirelmgt.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Clirelmgt.Controllers
{
    public partial class ObjectController : Controller
    {
        public ActionResult List(string ID)
        {
            if (string.IsNullOrWhiteSpace(ID))
            {
                ID = "TABLE";
            }

            List<ObjectModel> model = new List<ObjectModel>();
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                var result = dataContext.GetAppObjectByType(ID.ToUpperInvariant());
                foreach (var item in result)
                {
                    model.Add(new ObjectModel()
                    {
                        Name = item.Name,
                        ModuleName = item.ModuleName,
                        LinkTable = item.LinkTable,
                        Active = item.Active,
                        ObjectType = item.ObjectType
                    });
                }
            }

            return View(model);
        }

        [HttpPost]
        public ActionResult List(string ID, FormCollection form)
        {
            if (string.IsNullOrWhiteSpace(ID))
            {
                ID = "TABLE";
            }

            List<string> buttons = form.AllKeys.Where(obj => (obj.Contains("Btn_"))).ToList();
            if (buttons.Count > 0)
            {
                string currentAction = buttons[0].Replace("Btn_", "");
                if (currentAction == "Add")
                {
                    return RedirectToAction("New");
                }
                else if (currentAction == "Delete")
                {
                    List<string> selectedRows = form.AllKeys.Where(obj => (obj.Contains("SelectRow_"))).ToList();
                    using (CRMDataContext dataContext = new CRMDataContext())
                    {
                        foreach (string item in selectedRows)
                        {
                            string objectName = item.Replace("SelectRow_", "");
                            dataContext.DeleteObject(objectName.ToUpper(), ID.ToUpper());
                        }
                    }

                    List<ObjectModel> model = new List<ObjectModel>();
                    using (CRMDataContext dataContext = new CRMDataContext())
                    {
                        var result = dataContext.GetAppObjectByType(ID.ToUpperInvariant());
                        foreach (var item in result)
                        {
                            model.Add(new ObjectModel()
                            {
                                Name = item.Name,
                                ModuleName = item.ModuleName,
                                LinkTable = item.LinkTable,
                                Active = item.Active,
                                ObjectType = item.ObjectType
                            });
                        }
                    }

                    return View(model);
                }
            }

            return View();
        }

        public ActionResult New()
        {
            NewObject model = new NewObject();
            model.Modules = new Dictionary<int, string>();
            model.Tables = new List<string>();
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                var result = dataContext.GetModules();
                foreach (var item in result)
                {
                    model.Modules.Add(item.ModuleID, item.Name);
                }

                var tables = from obj in dataContext.AppObjects
                             where obj.ObjectType == "TABLE" && obj.Active == true
                             select new { obj.Name };

                foreach (var item in tables)
                {
                    model.Tables.Add(item.Name);
                }
            }

            return View(model);
        }

        [HttpPost]
        public ActionResult New(int Module, string ObjectType, string Name, string LinkTable, string Key)
        {
            if (ObjectType.ToUpper() == "TABLE")
            {
                LinkTable = "";
            }

            if (ObjectType.ToUpper() == "TABLE")
            {
                CreateNewTable(Module, ObjectType, Name, LinkTable, Key);
            }
            else if (ObjectType.ToUpper() == "LIST")
            {
                CreateNewListPage(Module, ObjectType, Name, LinkTable);
                ObjectType = "ListPage";
            }
            else if (ObjectType.ToUpper() == "CARD")
            {

            }

            return RedirectToAction(ObjectType, new { ID = Name });
        }

        private void CreateNewListPage(int Module, string ObjectType, string Name, string LinkTable)
        {
            MetaListPage metaListPage = new MetaListPage();
            List<MetaFormField> fields = new List<MetaFormField>();
            fields.Add(new MetaFormField()
            {
                 Name = "No"
            });

            fields.Add(new MetaFormField()
            {
                Name = "Description"
            });

            metaListPage.CardPage = Name;
            metaListPage.Fields = fields;
            metaListPage.Name = Name;
            string formXml = Utility.SerializeObject(metaListPage);

            using (CRMDataContext dataContext = new CRMDataContext())
            {
                dataContext.CreateNewObject(Name.ToUpperInvariant(), ObjectType.ToUpperInvariant(), Module, formXml, LinkTable);
            }
        }

        private void CreateNewTable(int Module, string ObjectType, string Name, string LinkTable, string Key)
        {
            List<MetaTableField> fields = new List<MetaTableField>();
            MetaTableField nofield = new MetaTableField()
            {
                DataType = FieldDataType.Key,
                FieldLength = 0,
                Labels = new List<MetaLanguage>(),
                Name = "No",
                NotNull = true
            };

            nofield.Labels.Add(new MetaLanguage() { LabelText = "No", LangID = "en-US" });
            fields.Add(nofield);

            MetaTableField descfield = new MetaTableField()
            {
                DataType = FieldDataType.Text,
                FieldLength = 100,
                Labels = new List<MetaLanguage>(),
                Name = "Description",
                NotNull = true
            };

            descfield.Labels.Add(new MetaLanguage() { LabelText = "Description", LangID = "en-US" });
            fields.Add(descfield);

            MetaTable table = new MetaTable()
            {
                PrefixKey = Key,
                Name = Name,
                Fields = fields
            };

            string tableXml = Utility.SerializeObject(table);

            using (CRMDataContext dataContext = new CRMDataContext())
            {
                dataContext.CreateNewObject(Name.ToUpperInvariant(), ObjectType.ToUpperInvariant(), Module, tableXml, LinkTable);
            }
        }

        public ActionResult Edit(string ID, string type)
        {
            NewObject model = new NewObject();
            model.Modules = new Dictionary<int, string>();
            model.Tables = new List<string>();
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                var result = dataContext.GetModules();
                foreach (var item in result)
                {
                    model.Modules.Add(item.ModuleID, item.Name);
                }

                var tables = from obj in dataContext.AppObjects
                             where obj.ObjectType == "TABLE" && obj.Active == true
                             select new { obj.Name };

                foreach (var item in tables)
                {
                    model.Tables.Add(item.Name);
                }

                var appObjects = from obj in dataContext.AppObjects
                                 where obj.ObjectType == type.ToUpperInvariant() && obj.Name == ID.ToUpperInvariant()
                                 select new { obj.Name, obj.ModuleID, obj.ObjectType, obj.LinkTable  };
                foreach (var item in appObjects)
                {
                    model.Name = item.Name;
                    model.SelectedModule = item.ModuleID;
                    model.SelectedTable = item.ObjectType;
                }
            }

            return View(model);
        }

        [HttpPost]
        public ActionResult Edit(int Module, string Name, string LinkTable, FormCollection form)
        {
            string ObjectType = Request.QueryString["type"];

            using (CRMDataContext dataContext = new CRMDataContext())
            {
                
            }

            return RedirectToAction("List", new { type = ObjectType });
        }

        public ActionResult Table(string ID)
        {
            List<MetaTableField> model = new List<MetaTableField>();
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                var result = from obj in dataContext.AppObjects
                             where obj.Name == ID.ToUpper() && obj.ObjectType == "TABLE"
                             select obj;

                foreach (var item in result)
                {
                    MetaTable table = Utility.DeserializeString<MetaTable>(item.Metadata) as MetaTable;
                    model = table.Fields;
                }
            }

            return View(model);
        }

        private List<MetaTableField> AddField(FormCollection form)
        {
            Dictionary<int, string> nameKeys = GetFieldValues(form, "Name_");
            Dictionary<int, string> labelKeys = GetFieldValues(form, "Labels_");
            Dictionary<int, string> dataTypeKeys = GetFieldValues(form, "DataType_");
            Dictionary<int, string> optionKeys = GetFieldValues(form, "Options_");
            Dictionary<int, string> lengthKeys = GetFieldValues(form, "FieldLength_");
            Dictionary<int, string> notnullKeys = GetFieldValues(form, "NotNull_");
            
            List<MetaTableField> model = new List<MetaTableField>();
            foreach (KeyValuePair<int, string> item in nameKeys)
            {
                string name = item.Value;
                string label = labelKeys.ContainsKey(item.Key) ? labelKeys[item.Key] : "";
                string dataType = dataTypeKeys.ContainsKey(item.Key) ? dataTypeKeys[item.Key] : "";
                string option = optionKeys.ContainsKey(item.Key) ? optionKeys[item.Key] : "";
                string length = lengthKeys.ContainsKey(item.Key) ? lengthKeys[item.Key] : "0";
                string notNull = notnullKeys.ContainsKey(item.Key) ? notnullKeys[item.Key] : "";

                MetaTableField tabField = new MetaTableField()
                {
                    DataType = (FieldDataType)Enum.Parse(typeof(FieldDataType), dataType),
                    FieldLength = Convert.ToUInt16(length),
                    Labels = new List<MetaLanguage>(),
                    Name = name,
                    Options = option.ToUpperInvariant(),
                    NotNull = notNull.ToLower() == "on" ? true : false
                };

                tabField.Labels.Add(new MetaLanguage() { LabelText = label, LangID = "en-US" });

                model.Add(tabField);
            }

            MetaTableField field = new MetaTableField()
            {
                DataType = FieldDataType.Key,
                FieldLength = 0,
                Labels = new List<MetaLanguage>(),
                Name = "",
                NotNull = false
            };

            field.Labels.Add(new MetaLanguage() { LabelText = "", LangID = "en-US" });
            model.Add(field);

            return model;
        }

        private List<MetaTableField> DeleteFields(FormCollection form)
        {
            Dictionary<int, string> selectKeys = GetFieldValues(form, "SelectRow_");
            Dictionary<int, string> nameKeys = GetFieldValues(form, "Name_");
            Dictionary<int, string> labelKeys = GetFieldValues(form, "Labels_");
            Dictionary<int, string> dataTypeKeys = GetFieldValues(form, "DataType_");
            Dictionary<int, string> optionKeys = GetFieldValues(form, "Options_");
            Dictionary<int, string> lengthKeys = GetFieldValues(form, "FieldLength_");
            Dictionary<int, string> notnullKeys = GetFieldValues(form, "NotNull_");

            List<MetaTableField> model = new List<MetaTableField>();
            foreach (KeyValuePair<int, string> item in nameKeys)
            {
                if (!selectKeys.ContainsKey(item.Key))
                {
                    string name = item.Value;
                    string label = labelKeys.ContainsKey(item.Key) ? labelKeys[item.Key] : "";
                    string dataType = dataTypeKeys.ContainsKey(item.Key) ? dataTypeKeys[item.Key] : "";
                    string option = optionKeys.ContainsKey(item.Key) ? optionKeys[item.Key] : "";
                    string length = lengthKeys.ContainsKey(item.Key) ? lengthKeys[item.Key] : "0";
                    string notNull = notnullKeys.ContainsKey(item.Key) ? notnullKeys[item.Key] : "";

                    MetaTableField tabField = new MetaTableField()
                    {
                        DataType = (FieldDataType)Enum.Parse(typeof(FieldDataType), dataType),
                        FieldLength = Convert.ToUInt16(length),
                        Labels = new List<MetaLanguage>(),
                        Name = name,
                        Options = option.ToUpperInvariant(),
                        NotNull = notNull.ToLower() == "on" ? true : false
                    };

                    tabField.Labels.Add(new MetaLanguage() { LabelText = label, LangID = "en-US" });

                    model.Add(tabField);
                }
            }

            return model;
        }

        private List<MetaTableField> SaveFields(FormCollection form)
        {
            Dictionary<int, string> nameKeys = GetFieldValues(form, "Name_");
            Dictionary<int, string> labelKeys = GetFieldValues(form, "Labels_");
            Dictionary<int, string> dataTypeKeys = GetFieldValues(form, "DataType_");
            Dictionary<int, string> optionKeys = GetFieldValues(form, "Options_");
            Dictionary<int, string> lengthKeys = GetFieldValues(form, "FieldLength_");
            Dictionary<int, string> notnullKeys = GetFieldValues(form, "NotNull_");

            List<MetaTableField> model = new List<MetaTableField>();
            foreach (KeyValuePair<int, string> item in nameKeys)
            {
                if (!string.IsNullOrWhiteSpace(item.Value))
                {
                    string name = item.Value;
                    string label = labelKeys.ContainsKey(item.Key) ? labelKeys[item.Key] : "";
                    string dataType = dataTypeKeys.ContainsKey(item.Key) ? dataTypeKeys[item.Key] : "";
                    string option = optionKeys.ContainsKey(item.Key) ? optionKeys[item.Key] : "";
                    string length = lengthKeys.ContainsKey(item.Key) ? lengthKeys[item.Key] : "0";
                    string notNull = notnullKeys.ContainsKey(item.Key) ? notnullKeys[item.Key] : "";

                    MetaTableField tabField = new MetaTableField()
                    {
                        DataType = (FieldDataType)Enum.Parse(typeof(FieldDataType), dataType),
                        FieldLength = Convert.ToUInt16(length),
                        Labels = new List<MetaLanguage>(),
                        Name = name,
                        Options = option.ToUpperInvariant(),
                        NotNull = notNull.ToLower() == "on" ? true : false
                    };

                    tabField.Labels.Add(new MetaLanguage() { LabelText = label, LangID = "en-US" });

                    model.Add(tabField);
                }
            }

            return model;
        }

        private Dictionary<int, string> GetFieldValues(FormCollection form, string keyName)
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            string[] keys = form.AllKeys.Where(obj => (obj.Contains(keyName))).ToArray();
            foreach (string key in keys)
            {
                string[] splitKey = key.Split('_');
                result.Add(Convert.ToInt32(splitKey[1]), form[key]);
            }

            return result;
        }

        [HttpPost]
        public ActionResult Table(FormCollection form)
        {
            List<MetaTableField> model = new List<MetaTableField>();
            string TableName = form["TableName"];
            try
            {
                List<string> buttons = form.AllKeys.Where(obj => (obj.Contains("Btn_"))).ToList();
                string currentAction = "";
                if (buttons.Count > 0)
                {
                    currentAction = buttons[0].Replace("Btn_", "");
                }

                if (currentAction == "Add")
                {
                    model = AddField(form);
                }
                else if (currentAction == "Delete")
                {
                    model = DeleteFields(form);
                }
                else if (currentAction == "Save")
                {
                    model = SaveFields(form);
                    TempData["alert"] = StoreTableMeta(TableName, "", model, false);
                }
                else if (currentAction == "SaveCompile")
                {
                    model = SaveFields(form);
                    TempData["alert"] = StoreTableMeta(TableName, "", model, true);
                }
            }
            catch(Exception ex)
            {
                TempData["alert"] = "Error while doing opertaion";
            }

            return PartialView("TableFieldList", model);
        }

        public string StoreTableMeta(string name, string prefixKey, List<MetaTableField> fields, bool compile)
        {
            string result = "";

            try
            {
                if (compile)
                {
                    CompileTable(name, fields);
                }

                MetaTable table = new MetaTable()
                {
                    PrefixKey = prefixKey,
                    Name = name,
                    Fields = fields
                };

                string tableXml = Utility.SerializeObject(table);
                using (CRMDataContext dataContext = new CRMDataContext())
                {
                    dataContext.UpdateObjectMetaData(name.ToUpperInvariant(), "TABLE", tableXml, compile);
                }

                result = "Table saved successfully!";
            }
            catch
            {
                if (compile)
                {
                    result = "Error while compiling and saving table metadata";
                }
                else
                {
                    result = "Error while saving table metadata";
                }
            }

            return result;
        }

        private void CompileTable(string name, List<MetaTableField> fields)
        {
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                List<GetTableColumnsResult> result = dataContext.GetTableColumns(name).ToList();
                if (result.Count > 0)
                {
                    string script = AlterTableScript(name, fields, result);
                    dataContext.ExecuteCommand(script);
                }
                else
                {
                    string script = CreateTableScript(name, fields);
                    dataContext.ExecuteCommand(script);
                }
            }
        }

        private string CreateTableScript(string name, List<MetaTableField> fields)
        {
            string script = string.Empty;
            string fieldScript = "";
            foreach (MetaTableField field in fields)
            {
                string nullValue = "NOT NULL";

                if(field.NotNull)
                {
                    nullValue = "NULL";
                }

                fieldScript += string.Format("	[{0}] {1} {2},", field.Name, GetSqlDataType(field.DataType, field.FieldLength), nullValue);
            }

            script = string.Format(CRMResource.CreateTableTemplate, name, fieldScript);
            return script;
        }

        private string AlterTableScript(string name, List<MetaTableField> metaFields, List<GetTableColumnsResult> tableFields)
        {
            string script = string.Empty;
            List<FieldColumn> metaColumns = new List<FieldColumn>();
            List<FieldColumn> tableColumns = new List<FieldColumn>();
            foreach (var item in metaFields)
            {
                metaColumns.Add(new FieldColumn()
                {
                    Name = item.Name,
                    DataType = GetSqlDataType(item.DataType, item.FieldLength),
                    IsNullable = item.NotNull ? "NO" : "YES"
                });
            }

            foreach (var item in tableFields)
            {
                if (item.ORDINAL_POSITION > 6)
                {
                    string dataType = item.DATA_TYPE;
                    if (item.CHARACTER_MAXIMUM_LENGTH.HasValue)
                    {
                        dataType = string.Format("{0}({1})", item.DATA_TYPE, item.CHARACTER_MAXIMUM_LENGTH);
                    }
                    else if (item.DATA_TYPE == "decimal")
                    {
                        dataType = "decimal(18,2)";   
                    }

                    tableColumns.Add(new FieldColumn()
                    {
                        Name = item.COLUMN_NAME,
                        DataType = dataType,
                        IsNullable = item.IS_NULLABLE
                    });
                }
            }

            foreach (var item in metaColumns)
            {

            }

            return script;
        }

        private string GetSqlDataType(FieldDataType dataType, int length)
        {
            string sqlDataType = string.Empty;
            switch (dataType)
            {
                case FieldDataType.Key:
                    sqlDataType = "varchar(30)";
                    break;
                case FieldDataType.Text:
                    sqlDataType = string.Format("nvarchar({0})", length);
                    break;
                case FieldDataType.MultilineText:
                    sqlDataType = string.Format("nvarchar({0})", length);
                    break;
                case FieldDataType.MaxText:
                    sqlDataType = "ntext";
                    break;
                case FieldDataType.Email:
                    sqlDataType = "nvarchar(200)";
                    break;
                case FieldDataType.URL:
                    sqlDataType = "nvarchar(200)";
                    break;
                case FieldDataType.Number:
                    sqlDataType = "int";
                    break;
                case FieldDataType.SmallNumber:
                    sqlDataType = "smallint";
                    break;
                case FieldDataType.Decimal:
                    sqlDataType = "decimal(18,2)";
                    break;
                case FieldDataType.Bool:
                    sqlDataType = "bit";
                    break;
                case FieldDataType.Date:
                    sqlDataType = "datetime";
                    break;
                case FieldDataType.Select:
                    sqlDataType = "nvarchar(200)";
                    break;
                case FieldDataType.Table:
                    sqlDataType = "varchar(30)";
                    break;
                default:
                    sqlDataType = "varchar(100)";
                    break;
            }

            return sqlDataType;
        }

        public ActionResult ListPage(string ID)
        {
            List<MetaFormField> formFields = new List<MetaFormField>();
            MetaListPage listPage = new MetaListPage();
            MetaTable table = new MetaTable();
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                var result = dataContext.GetAppObject(ID.ToUpper(), "LIST", false);
                foreach (var item in result)
                {
                    listPage = Utility.DeserializeString<MetaListPage>(item.AppMetaData) as MetaListPage;
                    table = Utility.DeserializeString<MetaTable>(item.AppMetaData) as MetaTable;
                }
            }

            return View();
        }

        [HttpPost]
        public ActionResult ListPage(string ID, FormCollection form)
        {
            return View();
        }
    }

    public class FieldColumn
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string DataType { get; set; }
        public string IsNullable { get; set; }
    }
}

